package com.ctshk.rpc.localfun.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ctshk.rpc.localfun.dto.*;
import com.ctshk.rpc.localfun.dto.es.LocalFunCandidateProductDto;
import com.ctshk.rpc.localfun.dto.es.LocalFunTypeDto;
import com.ctshk.rpc.localfun.entity.LocalFunProduct;
import com.ctshk.rpc.localfun.req.LocalFunPriceReq;
import com.ctshk.rpc.localfun.req.LocalFunProductListReq;
import com.ctshk.rpc.localfun.req.LocalFunReservationListReq;
import com.ctshk.rpc.localfun.req.LocalFunResourceReq;
import com.ctshk.rpc.localfun.req.cache.LocalFunProductCacheOpsReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

/**
 * @Description
 * @Author wansi <gaylen15527491065@yeah.net>
 * @Date 2021/3/23
 */
public interface LocalFunProductMapper extends BaseMapper<LocalFunProduct> {
    /**
     * 查询预定中心景点门票列表
     *
     * @param reservationListReq
     * @param page
     * @return
     */
    @Select("<script>"+"select tp.id id, tp.photos_json photosJson,tp.name name,tp.theme_json themeJson,tp.city_id cityId,tp.city_name cityName, tp.scenic_id scenicId, min(ts.cost_price) costPrice from t_scenic_product tp left join t_scenic_product_cost_strategy_spec_stock ts on tp.id = ts.product_id where 1=1\n" +
            "<if test=\"req.cityId != null\"> AND tp.city_id = #{req.cityId}</if> \n" +
            "<if test=\"req.fun_type != null\"> AND tp.fun_type = #{req.funType}</if> \n" +
            "GROUP BY tp.id" +
            "<if test=\"req.priceSort == 1\"> ORDER BY ts.cost_price</if> \n" +
            "<if test=\"req.priceSort == 0 or req.priceSort == 2\"> ORDER BY ts.cost_price DESC</if> \n" + "</script>")
    IPage<LocalFunReservationListDTO> queryList(@Param("req") LocalFunReservationListReq reservationListReq, IPage<LocalFunReservationListDTO> page);


    /**
     *
     * @return
     */
    @Select("<script>"+"SELECT\n" +
            "\tt1.id,\n" +
            "  t1.photos_json photosJson,\n" +
            "  t1.allow_package allowPackage,\n" +
            "  t6.strategy_id strategyId,\n" +
            "  t1.name,\n" +
            "  t1.city_name cityName,\n" +
            "  t1.fun_name funName,\n" +
            "  t1.start_sale_time_begin startSaleTimeBegin,\n" +
            "  t1.start_sale_time_end startSaleTimeEnd,\n" +
            "  t1.start_sale_time_type startSaleTimeType,\n" +
            "  t3.cost_price costPrice,\n" +
            "  t4.currency,\n" +
            "  min(t2.time_begin) timeBegin,\n" +
            "  max(t2.time_end) timeEnd,\n" +
            "  min(t6.time_begin) saleTimeBegin,\n" +
            "  max(t6.time_end) saleTimeEnd\n" +
            "FROM\n" +
            "\tt_local_fun_product t1\n" +
            "LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t2\n" +
            "on t1.id = t2.product_id\n" +
            "LEFT JOIN t_local_fun_product_cost_strategy_spec_stock t3\n" +
            "on t1.id = t3.product_id\n" +
            "LEFT JOIN t_local_fun_product_cost_strategy t4\n" +
            "on t1.id = t4.product_id\n" +
            "LEFT JOIN t_local_fun_product_sale_strategy t5\n" +
            "on t1.id=t5.product_id\n" +
            "LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t6\n" +
            "on t5.id = t6.strategy_id\n" +
            "WHERE t1.is_recommend = 1 and t1.status=1 and t1.sale_status = 1 group by t4.product_id  order by t1.gmt_modified DESC LIMIT 15 " + "</script>")
    List<LocalFunReservationListDTO> recommendedList();

    @Select("<script>"+" SELECT * from (\n" +
            "SELECT\n" +
            "\tt.id,\n" +
            "  t.`name`,\n" +
            "  t.fun_name funName,\n" +
            "  t.photos_json photosJson,\n" +
            "  t.city_name cityName,\n" +
            "  t.start_sale_time_begin startSaleTimeBegin,\n" +
            "  t.start_sale_time_end startSaleTimeEnd,\n" +
            "  t.start_sale_time_type startSaleTimeType,\n" +
            "  t0.currency,\n" +
            "  t1.cost_price costPrice,\n" +
            "  t6.adjust_type adjustType,\n" +
            "  t6.amount,\n" +
            "  t6.percent,\n" +
            "  t5.visable_type visableType,\n" +
            "  t6.relation_id relationId\n" +
//            "  t6.limit_num - IFNULL(sum(t7.sold_number), 0) soldNumber\n" +
            "FROM\n" +
            "\tt_local_fun_product t\n" +
            "LEFT JOIN t_local_fun_product_cost_strategy t0 ON t.id = t0.product_id\n" +
            "LEFT JOIN t_local_fun_product_cost_strategy_spec_stock t1 ON t0.id = t1.cost_id\n" +
            "LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t2 ON t0.id = t2.cost_id\n" +
            "LEFT JOIN t_local_fun_product_sale_strategy t3 ON t.id = t3.product_id\n" +
            "LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t4 ON t3.id = t4.strategy_id\n" +
            "LEFT JOIN t_local_fun_product_channel_offline t5 ON t3.id = t5.strategy_id\n" +
            "LEFT JOIN t_local_fun_product_channel_offline_self_custom t6 ON t5.id = t6.offline_id\n" +
//            "LEFT JOIN t_local_fun_product_sale_record t7 ON t.id = t7.product_id\n" +
//            "AND t5.channel_type = t7.check_type\n" +
            "WHERE\n" +
            "\tt.is_deleted = 0\n" +
            "AND t.is_recommend = 1\n" +
            "AND t.`status` = 1\n" +
            "AND t.id not in (select product_id from t_local_fun_product_channel_offline where visable_type IN ('1') and channel_type = 1 )\n" +
            "AND DATE_FORMAT(t2.time_begin, '%Y-%m-%d') &lt;= #{nowDate}\n" +
            "AND DATE_FORMAT(t2.time_end, '%Y-%m-%d') &gt;= #{nowDate}\n" +
            "AND DATE_FORMAT(t4.time_begin, '%Y-%m-%d') &lt;= #{nowDate}\n" +
            "AND DATE_FORMAT(t4.time_end, '%Y-%m-%d') &gt;= #{nowDate}\n" +
//            "<if test='relationIdList != null'>" +
//            "AND t6.relation_id IN \n" +
//            "<foreach collection='relationIdList' item='relationId' open='(' separator=',' close=')'>"+
//            "#{relationId}"+
//            "</foreach>"+
//            "</if>" +
            "GROUP BY\n" +
            "\tt.id\n" +
            "ORDER BY\n" +
            "\tt.gmt_create DESC\n" +
            "LIMIT 15\n" +
            ")t "+"</script>")
    List<LocalFunReservationListDTO> getRecommendedList(@Param("relationIdList") List<String> relationIdList, LocalDate nowDate);

    @Select("<script>"+"" +
            " SELECT\n" +
            " (\n" +
            " CASE t5.adjust_type\n" +
            " WHEN 1 THEN\n" +
            " t5.amount\n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t5.amount, 0)\n" +
            " WHEN '2' THEN\n" +
            " IFNULL(y2.cost_price, 0) * (IFNULL(t5.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) agentFee,\n" +
            " IFNULL(y2.cost_price, 0) price," +
            " t4.visable_type,\n" +
            " t5.agent_id id,\n" +
            " t5.agent_name agentName\n" +
            " FROM\n" +
            " t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy y1 ON t1.id = y1.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline t4 ON t1.id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline_agent_custom t5 ON t4.id = t5.offline_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(y3.time_begin, '%Y-%m-%d') &lt;= #{nowDate}\n" +
            " AND DATE_FORMAT(y3.time_end, '%Y-%m-%d') &gt;= #{nowDate}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{nowDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{nowDate}\n" +
            " AND t1.id = #{req.productId}\n" +
            " <if test='req.attr1Id != null'>" +
            " AND y2.attr1_id = #{req.attr1Id}\n" +
            " </if>" +
            " <if test='req.attr2Id != null'>" +
            " AND y2.attr2_id = #{req.attr2Id}\n" +
            " </if>" +
            " <if test='req.agentName != null'>" +
            " AND t5.agent_name like concat(concat('%', #{req.agentName}), '%')"+
            " </if>" +
            " GROUP BY\n" +
            " t1.id" +
            ""+"</script>")
    IPage<AgentDto> queryAgentList(@Param("req") AgentReq req, @Param("nowDate") LocalDate now, IPage<LocalFunProduct> iPage);


    @Select("<script>"+"" +
            " SELECT\n" +
            " (\n" +
            " CASE t5.adjust_type\n" +
            " WHEN 1 THEN\n" +
            " t5.amount\n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t5.amount, 0)\n" +
            " WHEN '2' THEN\n" +
            " IFNULL(y2.cost_price, 0) * (IFNULL(t5.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) agentFee,\n" +
            " IFNULL(y2.cost_price, 0) price," +
            " t4.visable_type,\n" +
            " t5.agent_id id,\n" +
            " t5.agent_name agentName\n" +
            " FROM\n" +
            " t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy y1 ON t1.id = y1.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline t4 ON t1.id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline_agent_custom t5 ON t4.id = t5.offline_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(y3.time_begin, '%Y-%m-%d') &lt;= #{nowDate}\n" +
            " AND DATE_FORMAT(y3.time_end, '%Y-%m-%d') &gt;= #{nowDate}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{nowDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{nowDate}\n" +
            " AND t1.id = #{productId}\n" +
            " AND t4.channel_type = 2\n"+
            " <if test='attr1Id != null'>" +
            " AND y2.attr1_id = #{attr1Id}\n" +
            " </if>" +
            " <if test='attr2Id != null'>" +
            " AND y2.attr2_id = #{attr2Id}\n" +
            " </if>" +
            " GROUP BY\n" +
            " t1.id" +
            ""+"</script>")
    List<AgentDto> getAgent(@Param("productId") Long productId,@Param("attr1Id") Long attr1Id, @Param("attr2Id")Long attr2Id, @Param("nowDate") LocalDate now);

    @Select("<script>"+"" +
            " SELECT\n" +
            " (\n" +
            " CASE t5.adjust_type\n" +
            " WHEN 1 THEN\n" +
            " t5.amount\n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t5.amount, 0)\n" +
            " WHEN '2' THEN\n" +
            " IFNULL(y2.cost_price, 0) * IFNULL(y1.exchange_rate, 0) * (IFNULL(t5.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) agentFee,\n" +
            " IFNULL(y2.cost_price, 0) * IFNULL(y1.exchange_rate, 0) price," +
            " t4.visable_type,\n" +
            " t5.agent_id id,\n" +
            " t1.reserve_charge_type reserveChargeType,\n" +
            " t1.reserve_charge reserveCharge,\n" +
            " t5.agent_name agentName\n" +
            " FROM\n" +
            " t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy y1 ON t1.id = y1.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline t4 ON t1.id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline_agent_custom t5 ON t4.id = t5.offline_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(y3.time_begin, '%Y-%m-%d') &lt;= #{nowDate}\n" +
            " AND DATE_FORMAT(y3.time_end, '%Y-%m-%d') &gt;= #{nowDate}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{nowDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{nowDate}\n" +
            " AND t1.id = #{req.productId} AND t4.channel_type =2 \n" +
            " <if test='req.attr1Id != null'>" +
            " AND y2.attr1_id = #{req.attr1Id}\n" +
            " </if>" +
            " <if test='req.attr2Id != null'>" +
            " AND y2.attr2_id = #{req.attr2Id}\n" +
            " </if>" +
            " <if test='req.agentName != null'>" +
            " AND t5.agent_name like concat(concat('%', #{req.agentName}), '%')"+
            " </if>" +
            " GROUP BY\n" +
            " t1.id" +
            ""+"</script>")
    List<AgentDto> queryAgentList2(@Param("req") AgentReq req, @Param("nowDate") LocalDate now);

    @Select("<script>"+"SELECT\n" +
            " id,\n" +
            " product_code,\n" +
            " NAME,\n" +
            " city_name,\n" +
            " source_type,\n" +
            " fun_name,\n" +
            " supplier_name,\n" +
            " intended_user,\n" +
            " charge_user_id,\n" +
            " charge_user,\n" +
            " sale_status,\n" +
            " allow_package,\n" +
            " approval_status\n" +
            " FROM\n" +
            " t_local_fun_product "+
            " where 1=1 " +
            " <if test='req.cityId != null'>" +
            " AND city_id = #{req.cityId}\n" +
            " </if>" +
            " <if test='req.productCode != null'>" +
            " AND product_code = #{req.productCode}\n" +
            " </if>" +
            " <if test='req.cityName != null'>" +
            " AND city_name LIKE CONCAT('%',#{req.cityName},'%')\n" +
            " </if>" +
            " <if test='req.supplierId != null'>" +
            " AND supplier_id = #{req.supplierId}\n" +
            " </if>" +
            " <if test='req.supplierName != null'>" +
            " AND supplier_name LIKE CONCAT('%',#{req.supplierName},'%')\n" +
            " </if>" +
            " <if test='req.funType != null'>" +
            " AND fun_type = #{req.funType}\n" +
            " </if>" +
            " <if test='req.funName != null'>" +
            " AND fun_name LIKE CONCAT('%',#{req.funName},'%')\n" +
            " </if>" +
            " <if test='req.saleStatus != null'>" +
            " AND sale_status = #{req.saleStatus}\n" +
            " </if>" +
            " <if test='req.status != null'>" +
            " AND status = #{req.status}\n" +
            " </if>" +
            " <if test='req.approvalStatus != null'>" +
            " AND approval_status = #{req.approvalStatus}\n" +
            " </if>" +
            " <if test='req.allowPackage != null'>" +
            " AND allow_package = #{req.allowPackage}\n" +
            " </if>" +
            " <if test='req.name != null'>" +
            " AND name LIKE CONCAT('%',#{req.name},'%')\n" +
            " </if>" +
            " <if test='data != null and data.size > 0'>" +
            " AND (charge_user_id IN \n" +
            " <foreach collection='data' item='userId' open='(' separator=',' close=')'>"+
            " #{userId}"+
            " </foreach> or create_id IN \n"+
            " <foreach collection='data' item='userId' open='(' separator=',' close=')'>"+
            " #{userId}"+
            " </foreach> )\n"+
            " </if>" +

            " order by gmt_modified desc</script>")
    Page<LocalFunProduct> querySourceProductList(@Param("req") LocalFunResourceReq req, List<Long> data, IPage<LocalFunProduct> iPage);


    @Select("<script>"+"SELECT\n" +
            " id,\n" +
            " product_code,\n" +
            " NAME,\n" +
            " city_name,\n" +
            " source_type,\n" +
            " fun_name,\n" +
            " supplier_name,\n" +
            " intended_user,\n" +
            " charge_user,\n" +
            " charge_user_id,\n" +
            " sale_status,\n" +
            " status,\n" +
            " is_recommend,\n" +
            " allow_package,\n" +
            " approval_status\n" +
            " FROM\n" +
            " t_local_fun_product "+
            " where 1=1 and sale_status = 1 and approval_status = 3" +
            " <if test='req.cityId != null'>" +
            " AND city_id = #{req.cityId}\n" +
            " </if>" +
            " <if test='req.cityName != null'>" +
            " AND city_name LIKE CONCAT('%',#{req.cityName},'%')\n" +
            " </if>" +
            " <if test='req.supplierId != null'>" +
            " AND supplier_id = #{req.supplierId}\n" +
            " </if>" +
            " <if test='req.supplierName != null'>" +
            " AND supplier_name LIKE CONCAT('%',#{req.supplierName},'%')\n" +
            " </if>" +
            " <if test='req.funType != null'>" +
            " AND fun_type = #{req.funType}\n" +
            " </if>" +
            " <if test='req.funName != null'>" +
            " AND fun_name LIKE CONCAT('%',#{req.funName},'%')\n" +
            " </if>" +
            " <if test='req.sourceType != null'>" +
            " AND source_type = #{req.sourceType}\n" +
            " </if>" +
            " <if test='req.isRecommend != null'>" +
            " AND is_recommend = #{req.isRecommend}\n" +
            " </if>" +
            " <if test='req.status != null'>" +
            " AND status = #{req.status}\n" +
            " </if>" +
            " <if test='req.name != null'>" +
            " AND name LIKE CONCAT('%',#{req.name},'%')\n" +
            " </if>" +
            " <if test='data != null and data.size > 0'>" +
            " AND (charge_user_id IN \n" +
            " <foreach collection='data' item='userId' open='(' separator=',' close=')'>"+
            " #{userId}"+
            " </foreach> or create_id IN \n"+
            " <foreach collection='data' item='userId' open='(' separator=',' close=')'>"+
            " #{userId}"+
            " </foreach> )\n"+
            " </if>" +

            " order by gmt_modified desc</script>")
    Page<LocalFunProduct> queryProductList(@Param("req") LocalFunProductListReq req, @Param("data") List<Long> data, IPage<LocalFunProduct> iPage);

    @Select("<script>"+"SELECT\n" +
            " id,\n" +
            " product_code,\n" +
            " NAME,\n" +
            " city_name,\n" +
            " source_type,\n" +
            " fun_name,\n" +
            " supplier_name,\n" +
            " intended_user,\n" +
            " charge_user,\n" +
            " sale_status,\n" +
            " status,\n" +
            " photos_json photosJson,\n" +
            " is_recommend,\n" +
            " allow_package,\n" +
            " approval_status\n" +
            " FROM\n" +
            " t_local_fun_product "+
            " where 1=1 and status = 1 and sale_status = 1  " +
            " <if test='req.cityId != null'>" +
            " AND city_id = #{req.cityId}\n" +
            " </if>" +
            " <if test='req.cityName != null'>" +
            " AND city_name LIKE CONCAT('%',#{req.cityName},'%')\n" +
            " </if>" +
            " <if test='req.funType != null'>" +
            " AND fun_type = #{req.funType}\n" +
            " </if>" +
            " <if test='req.funName != null'>" +
            " AND fun_name LIKE CONCAT('%',#{req.funName},'%')\n" +
            " </if>" +
            " <if test='req.name != null'>" +
            " AND name LIKE CONCAT('%',#{req.name},'%')\n" +
            " </if>" +

            " order by gmt_modified desc</script>")
    IPage<LocalFunProduct> queryReserveProductList(IPage<LocalFunProduct> iPage, @Param("req") LocalFunReservationListReq req);

    @Select("<script>"+" select t1.id,y2.attr1_id,y2.attr2_id,IFNULL(y2.cost_price,0) costPrice,\n" +
            " (CASE t4.adjust_type\n" +
            "     WHEN '1' THEN\n" +
            "     IFNULL(t4.amount, 0)\n" +
            "     WHEN '2' THEN\n" +
            "     IFNULL(y2.cost_price, 0) * IFNULL(y1.exchange_rate,0) * (IFNULL(t4.percent, 0) * 0.01)\n" +
            "     END\n" +
            " ) as addFee,\n" +
            " (CASE t1.reserve_charge_type\n" +
            "     WHEN '1' THEN\n" +
            "     IFNULL(t1.reserve_charge, 0)\n" +
            "     WHEN '2' THEN\n" +
            "     IFNULL(y2.cost_price, 0)* IFNULL(y1.exchange_rate,0) * (IFNULL(t1.reserve_charge, 0) * 0.01)\n" +
            "     END\n" +
            " ) as serviceFee,y1.currency_id as currencyId ,y1.exchange_rate exchangeRate,y2.surplus_stock surplusStock \n" +
            " from  t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy y1 ON t1.id = y1.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " left join t_local_fun_product_channel_online_self_platform t4 ON t4.strategy_id = t2.id\n" +
            " WHERE " +
            " DATE_FORMAT(y3.time_begin, '%Y-%m-%d') &lt;= #{req.queryDate}\n" +
            " AND DATE_FORMAT(y3.time_end, '%Y-%m-%d') &gt;= #{req.queryDate}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{req.queryDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{req.queryDate}\n" +
            " and t1.id=#{req.productId}\n" +
            " and t4.channel_id=#{req.channelId}\n" +
            " <if test='req.attrId1 != null'>" +
            " AND y2.attr1_id = #{req.attrId1}\n" +
            " </if>" +
            " <if test='req.attrId2 != null'>" +
            " AND y2.attr2_id = #{req.attrId2}\n" +
            " </if>" +
            " group by t1.id </script>")
    LocalFunPriceDTO queryPriceById(@Param("req") LocalFunPriceReq req);

    @Select("<script>select t5.attr1_id attr1Id,t5.attr2_id attr2Id," +
            " t5.attr1_name attr1Name,\n" +
            " t5.attr2_name attr2Name,\n" +
            " t1.visable_type visableType,t2.relation_id relationId,t0.product_id productId,t2.adjust_type adjustType,t2.amount," +
            " t2.percent,t2.is_limit isLimit,t2.limit_num limitNum," +
            //成本價*匯率+加幅費
            " (case t2.adjust_type when '1' then  (IFNULL(t5.cost_price,0) *IFNULL(t4.exchange_rate,0)) + t2.amount " +
            //加幅費=成本價*匯率*百分比
            " when '2' then (IFNULL(t5.cost_price,0) * IFNULL(t4.exchange_rate,0)) + (IFNULL(t5.cost_price,0) * IFNULL(t4.exchange_rate,0)) * (t2.percent * 0.01) end) costAndFee," +
            " t5.cost_price costPrice,t5.total_stock solidStock,t4.exchange_rate exchangeRate from \n" +
            " t_local_fun_product_sale_strategy t0\n" +
            " left join t_local_fun_product_sale_strategy_vaild_date t3 on t0.id = t3.strategy_id\n" +
            " left join t_local_fun_product_channel_offline t1 on t0.id = t1.strategy_id\n" +
            " left join t_local_fun_product_channel_offline_self_custom t2 on t1.id = t2.offline_id \n" +
            " left join t_local_fun_product_cost_strategy t4 on t0.product_id = t4.product_id\n" +
            " left join t_local_fun_product_cost_strategy_spec_stock t5 on t4.id = t5.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t6 ON t4.id = t6.cost_id\n" +
            " where \n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " and t0.product_id=#{productId}\n" +
            " and t2.offline_id is not null\n" +
            " group by t5.attr1_id,t5.attr2_id </script>")
    List<LocalFunAddPriceDTO> queryOffLineSalePrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate);

    @Select("<script>select t5.attr1_id attr1Id,t5.attr2_id attr2Id," +
            " t5.attr1_name attr1Name,\n" +
            " t5.attr2_name attr2Name,\n" +
            "t1.visable_type visableType,t2.agent_id relationId,t0.product_id productId,t2.adjust_type adjustType,t2.amount," +
            " t2.percent,t2.is_limit isLimit,t2.limit_num limitNum," +
            //成本價*匯率+加幅費
            "(case t2.adjust_type when '1' then  (IFNULL(t5.cost_price,0) *IFNULL(t4.exchange_rate,0)) + t2.amount " +
            //加幅費=成本價*匯率*百分比
            " when '2' then (IFNULL(t5.cost_price,0) * IFNULL(t4.exchange_rate,0)) + (IFNULL(t5.cost_price,0) * IFNULL(t4.exchange_rate,0)) * (t2.percent * 0.01) end) costAndFee," +
            " t5.cost_price costPrice,t5.total_stock solidStock,t4.exchange_rate exchangeRate from \n" +
            " t_local_fun_product_sale_strategy t0\n" +
            " left join t_local_fun_product_sale_strategy_vaild_date t3 on t0.id = t3.strategy_id\n" +
            " left join t_local_fun_product_channel_offline t1 on t0.id = t1.strategy_id\n" +
            " left join t_local_fun_product_channel_offline_agent_custom t2 on t1.id = t2.offline_id \n" +
            " left join t_local_fun_product_cost_strategy t4 on t0.product_id = t4.product_id\n" +
            " left join t_local_fun_product_cost_strategy_spec_stock t5 on t4.id = t5.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t6 ON t4.id = t6.cost_id\n" +
            " where \n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " and t0.product_id=#{productId}\n" +
            " and t2.offline_id is not null\n" +
            " group by t5.attr1_id,t5.attr2_id </script>")
    List<LocalFunAddPriceDTO> querySaleAgentPrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate);

    @Select("<script>SELECT\n" +
            " t5.attr1_id attr1Id,\n" +
            " t5.attr2_id attr2Id,\n" +
            " t5.attr1_name attr1Name,\n" +
            " t5.attr2_name attr2Name,\n" +
            " t0.product_id productId,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t5.total_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_local_fun_product_sale_strategy t0\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_online_self_platform t2 ON t2.strategy_id = t0.id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy t4 ON t0.product_id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t0.product_id = #{productId}\n" +
            " AND t2.channel_name is not null\n" +
            " AND t2.channel_id is not null\n" +
            " AND t2.channel_id=#{relId}\n" +
            " GROUP BY\n" +
            " t5.attr1_id,t5.attr2_id</script>")
    List<LocalFunAddPriceDTO> querySaleOfficePrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate, @Param("relId") Long relId);

    @Select("<script>SELECT\n" +
            " t5.attr1_id attr1Id,\n" +
            " t5.attr2_id attr2Id,\n" +
            " t5.attr1_name attr1Name,\n" +
            " t5.attr2_name attr2Name,\n" +
            " t0.product_id productId,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t5.total_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_local_fun_product_sale_strategy t0\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_online_self_platform t2 ON t2.strategy_id = t0.id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy t4 ON t0.product_id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t0.product_id = #{productId}\n" +
//            " AND t2.channel_name is not null\n" +
//            " AND t2.channel_id is not null\n" +
            " GROUP BY\n" +
            " t5.attr1_id,t5.attr2_id</script>")
    List<LocalFunAddPriceDTO> queryNullSaleOfficePrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate, @Param("relId") Long relId);


    @Select("<script>SELECT\n" +
            " t5.attr1_id attr1Id,\n" +
            " t5.attr2_id attr2Id,\n" +
            " t5.attr1_name attr1Name,\n" +
            " t5.attr2_name attr2Name,\n" +
            " t0.product_id productId,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t5.total_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_local_fun_product_sale_strategy t0\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_online_third_platform t2 ON t2.strategy_id = t0.id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy t4 ON t0.product_id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t0.product_id = #{productId}\n" +
            " AND t2.platform_name is not null\n" +
            " AND t2.platform_id=#{relId}\n" +
            " GROUP BY\n" +
            " t5.attr1_id,t5.attr2_id</script>")
    List<LocalFunAddPriceDTO> querySaleThirdPrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate, @Param("relId") Long relId);

    @Select("<script>SELECT\n" +
            " t5.attr1_id attr1Id,\n" +
            " t5.attr2_id attr2Id,\n" +
            " t5.attr1_name attr1Name,\n" +
            " t5.attr2_name attr2Name,\n" +
            " t0.product_id productId,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t5.total_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_local_fun_product_sale_strategy t0\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_online_third_platform t2 ON t2.strategy_id = t0.id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy t4 ON t0.product_id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t0.product_id = #{productId}\n" +
            " AND t2.platform_name is not null\n" +
            " AND t2.platform_id is not null\n" +
            " GROUP BY\n" +
            " t5.attr1_id,t5.attr2_id</script>")
    List<LocalFunAddPriceDTO> queryNullSaleThirdPrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate, @Param("relId") Long relId);


    @Select("<script>" +
            " SELECT\n" +
            " t.id productId,\n" +
            " t.product_code productCode,\n" +
            " t.city_id cityId,\n" +
            " t.city_name cityName,\n" +
            " t.use_instructions useInstructions,\n" +
            " t.customer_notice customerNotice,\n" +
            " t.cancel_policy cancelPolicy,\n" +
            " t.name productName,\n" +
            " t.fun_name funName,\n" +
            " t.fun_type funType,\n" +
            " t.cover_image coverImage,\n" +
            " t.theme_json themeJson,\n" +
            " t.future_json futureJson,\n" +
            " t.photos_json photosJson,\n" +
            " t.is_recommend isRecommend,\n" +
            " t.product_introduces productIntroduces,\n" +
            " t.reserve_charge_type reserveChargeType,\n" +
            " t.reserve_charge reserveCharge,\n" +
            " t5.attr1_id attrId1,\n" +
            " t5.attr1_name attr1Name,\n" +
            " t5.attr2_id attrId2,\n" +
            " t5.attr2_name attr2Name,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.channel_id channelId,\n" +
            " t2.channel_id relationId,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t5.total_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_local_fun_product t \n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t0 ON t.id = t0.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_online_self_platform t2 ON t2.strategy_id = t0.id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy t4 ON t0.product_id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t2.channel_name is not null\n" +
            " AND t2.channel_id is not null\n" +
            " AND t.start_sale_time_type = 1\n" +
            " AND t.status = 1 and t.sale_status = 1 \n" +
            " <if test='req != null and req.ids != null and req.ids.size != 0 '>" +
            " AND t.id IN "+
            " <foreach collection='req.ids' item='id' open='(' separator=',' close=')'>"+
            " #{id}"+
            " </foreach>"+
            " </if> "+
            " <if test='req != null and req.startTime != null'>" +
            " AND DATE_FORMAT(t.gmt_modified, '%Y-%m-%d') &gt;= #{req.startTime}\n" +
            " </if>" +
            " <if test='req != null and req.endTime != null'>" +
            " AND DATE_FORMAT(t.gmt_modified, '%Y-%m-%d') &lt;= #{req.endTime}\n" +
            " </if>" +
            " group by t2.channel_id,t5.attr1_id,t5.attr2_id" +

            " union all " +

            " SELECT\n" +
            " t.id productId,\n" +
            " t.product_code productCode,\n" +
            " t.city_id cityId,\n" +
            " t.city_name cityName,\n" +
            " t.use_instructions useInstructions,\n" +
            " t.customer_notice customerNotice,\n" +
            " t.cancel_policy cancelPolicy,\n" +
            " t.name productName,\n" +
            " t.fun_name funName,\n" +
            " t.fun_type funType,\n" +
            " t.cover_image coverImage,\n" +
            " t.theme_json themeJson,\n" +
            " t.future_json futureJson,\n" +
            " t.photos_json photosJson,\n" +
            " t.is_recommend isRecommend,\n" +
            " t.product_introduces productIntroduces,\n" +
            " t.reserve_charge_type reserveChargeType,\n" +
            " t.reserve_charge reserveCharge,\n" +
            " t5.attr1_id attrId1,\n" +
            " t5.attr1_name attr1Name,\n" +
            " t5.attr2_id attrId2,\n" +
            " t5.attr2_name attr2Name,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.channel_id channelId,\n" +
            " t2.channel_id relationId,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t5.total_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_local_fun_product t \n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t0 ON t.id = t0.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_online_self_platform t2 ON t2.strategy_id = t0.id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy t4 ON t0.product_id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t.start_sale_time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t.start_sale_time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t2.channel_name is not null\n" +
            " AND t2.channel_id is not null\n" +
            " AND t.start_sale_time_type = 2\n" +
            " AND t.status = 1 and t.sale_status = 1 \n" +
            " <if test='req != null and req.ids != null and req.ids.size != 0 '>" +
            " AND t.id IN "+
            " <foreach collection='req.ids' item='id' open='(' separator=',' close=')'>"+
            " #{id}"+
            " </foreach>"+
            " </if> "+
            " <if test='req != null and req.startTime != null'>" +
            " AND DATE_FORMAT(t.gmt_modified, '%Y-%m-%d') &gt;= #{req.startTime}\n" +
            " </if>" +
            " <if test='req != null and req.endTime != null'>" +
            " AND DATE_FORMAT(t.gmt_modified, '%Y-%m-%d') &lt;= #{req.endTime}\n" +
            " </if>" +
            " group by t2.channel_id,t5.attr1_id,t5.attr2_id" +
            "</script>")
    List<LocalFunCandidateProductDto> queryCandidateProduct(@Param(("departureDate")) LocalDate now, @Param("req") LocalFunProductCacheOpsReq req);

    @Select("<script>select t.fun_name funName,t.fun_type funType,t.id productId" +
            " FROM\n" +
            " t_local_fun_product t \n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t0 ON t.id = t0.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_online_self_platform t2 ON t2.strategy_id = t0.id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy t4 ON t0.product_id = t4.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t2.channel_name is not null\n" +
            " AND t2.channel_id is not null\n" +
            " AND t.status = 1\n" +
            " AND t.city_id = #{cityId} group by t.id\n" +
            "</script>"
    )
    List<LocalFunTypeDto> getProductCount(@Param("cityId") Long cityId, @Param("channelId") Long channelId, @Param("departureDate") LocalDate now);
}
